import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
/**
 * Created by 罗天瑞 on 2016/10/29.
 */
public class Main {
    public static void main(String[] args) throws IOException
    {
        //创建Workbook以及Sheet
        InputStream inputFile = new FileInputStream("data/input/Sample.xls");
        POIFSFileSystem fileSystem = new POIFSFileSystem(inputFile);
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workbook.getSheetAt(0);
        FileOutputStream outputFile = new FileOutputStream("data/output/Sample2.xls");
        HSSFWorkbook workbook1 = new HSSFWorkbook();
        HSSFSheet sheet1 = workbook1.createSheet("excelprocess");

        //对样板excel读取并写入新的excel中
        for(int i=sheet.getFirstRowNum();i<sheet.getLastRowNum()+1;i++)
        {
            HSSFRow row = sheet.getRow(i);
            HSSFRow row1 = sheet1.createRow(i);
            for(int j=row.getFirstCellNum();j<row.getLastCellNum();j++)
            {
                HSSFCell cell = row.getCell(j);
                HSSFCell cell1 = row1.createCell(j);
                CellType type = cell.getCellTypeEnum();
                if(cell.getCellTypeEnum()== CellType.STRING)
                {
                    String str = cell.getStringCellValue();
                    cell1.setCellValue(str);
                }
                if(cell.getCellTypeEnum()==CellType.NUMERIC)
                {
                    double number = cell.getNumericCellValue();
                    cell1.setCellValue(number);
                }
            }
        }

        //设置公式
        HSSFRow row = sheet1.getRow(2);
        row.createCell(5).setCellFormula("sum(C3:E3)");
        row = sheet1.getRow(3);
        row.createCell(5).setCellFormula("sum(C4:E4)");
        row = sheet1.getRow(4);
        row.createCell(5).setCellFormula("sum(C5:E5)");
        row = sheet1.getRow(5);
        row.createCell(5).setCellFormula("sum(C6:E6)");
        row = sheet1.getRow(6);
        row.createCell(2).setCellFormula("average(C3:C6)");
        row.createCell(3).setCellFormula("average(D3:D6)");
        row.createCell(4).setCellFormula("average(E3:E6)");
        row.createCell(5).setCellFormula("average(F3:F6)");

        //设置单元格格式
        row = sheet1.createRow(7);
        HSSFCell cell = row.createCell(4);
        cell.setCellValue(new Date());
        HSSFCellStyle style=workbook1.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        cell.setCellStyle(style);

        //合并单元格
        CellRangeAddress region = new CellRangeAddress(0,0,0,5);
        sheet1.addMergedRegion(region);
        region = new CellRangeAddress(7,7,4,5);
        sheet1.addMergedRegion(region);

        //单元格对齐
        row = sheet1.getRow(0);
        cell = row.getCell(0);
        style = workbook1.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        cell.setCellStyle(style);
        row = sheet1.getRow(1);
        for(int i =1;i<row.getLastCellNum();i++)
        {
            style = workbook1.createCellStyle();
            cell = row.getCell(i);
            style.setAlignment(HorizontalAlignment.RIGHT);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            cell.setCellStyle(style);
        }

       //输出excel
        workbook1.write(outputFile);
        outputFile.close();

        //图像处理验证
        CutImage cutImage = new CutImage();
        ShrinkImage shrinkImage = new ShrinkImage();
        EnlargeImage enlargeImage = new EnlargeImage();
        cutImage.CutImage();
        shrinkImage.ShrinkImage();
        enlargeImage.EnlargeImage();
    }
}
